package database;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Database {
	private Connection connection = null;

	@SuppressWarnings("unused")
	private String database_name = null;

	public Database(String db_name) {
		try {
			this.database_name = db_name;
			this.connect();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public boolean connect() {
		try {
			Class.forName("org.postgresql.Driver");
			connection = DriverManager.getConnection(
					"jdbc:postgresql://127.0.0.1:5432/restoreservation",
					"mrkhai", "123456");
			if (connection != null) {
				return true;
			} else {
				System.out.println("Failed to make connection!");
			}
			return false;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			return false;
		}

	}

	public String[] getColsTable(String table_name) throws SQLException {
		if (this.connection != null) {
			String str_sql = "select * from " + table_name;
			Statement stat = this.connection.createStatement();
			ResultSet rs = stat.executeQuery(str_sql);
			if (rs != null) {
				ResultSetMetaData mt = rs.getMetaData();
				int col_num = mt.getColumnCount();
				String[] fields = new String[col_num];
				for (int i = 0; i < col_num; i++)
					fields[i] = mt.getColumnLabel(i + 1);
				return fields;
			} else
				return null;
		} else
			return null;
	}

	/**
	 * 
	 * @parametre sql le sql query
	 * @return : ArrayList des données d'un tableau
	 * @throws SQLException
	 */
	
	public ArrayList<ArrayList<String>> get(String sql) {
		ArrayList<ArrayList<String>> datas = new ArrayList<ArrayList<String>>();
		
		try {
			if (this.connection == null)
				return null;
			ResultSet rs;
			Statement stat = this.connection.createStatement();
			rs = stat.executeQuery(sql);
			if (rs == null)
				return null;
			ResultSetMetaData mt = rs.getMetaData();
			int col_num = mt.getColumnCount();
			String[] fields = new String[col_num];
			for (int i = 0; i < col_num; i++)
				fields[i] = mt.getColumnLabel(i + 1);
			int field_len = fields.length;
			ArrayList<String> row;
			while (rs.next()) {
				row = new ArrayList<String>();
				for (int i = 0; i < field_len; i++) {
					if (rs.getString(fields[i]) != null)
						row.add(rs.getString(fields[i]));
				}
				datas.add(row);
			}
			rs.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return datas;
	}

	/**
	 * 
	 * @param sql
	 *            sql query
	 * @return : 0 si il y a une erreur d'excécution, au contraire, retourner 1
	 * @throws Exception
	 */
	public int set(String sql) {
		int res = 0;
		try {
			if (this.connection != null) {
				Statement stat = this.connection.createStatement();
				res = stat.executeUpdate(sql);
				stat.close();
			}
			this.disConnect();
			this.connect();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return res;
	}

	/**
	 * Déconnexion la base de données
	 * 
	 * @throws SQLException
	 */
	private void disConnect() throws SQLException {
		if (this.connection != null) {
			this.connection.close();
			this.connection = null;
		}
	}

	/**
	 * La déstruction
	 */
	public void finalize() {
		if (this.connection != null)
			try {
				this.disConnect();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public static void main(String[] argv) {
/*
		System.out.println("-------- PostgreSQL "
				+ "JDBC Connection Testing ------------");
		Database database;
		ArrayList<Map<String, String>> data;
		try {
			database = new Database("restoreservation");
			data = database.get("select * from category");
			System.out.println(data);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		*/

	}
}